package org.jeecg.modules.clockRecord.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.clockRecord.entity.ClockRecord;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * @Description: 打卡记录
 * @Author: jeecg-boot
 * @Date:   2025-01-26
 * @Version: V1.0
 */
public interface ClockRecordMapper extends BaseMapper<ClockRecord> {

    @Select("SELECT " +
            "c.admit_name, c.work_number, c.face_url, c.classes_type, sd.depart_name as dept, ssd.depart_name as classes, " +
            "c.admit_guid, c.in_time, c.out_time, c.abnormal_type, c.solve_reason, c.abnormal, c.solve, c.deputy_supervisor, c.classes_id " +
            "FROM " +
            "clock_record c left join sys_depart sd on sd.id = c.dept left join sys_depart ssd on ssd.id = c.classes " +
            "WHERE " +
            "DATE(c.date) = CURDATE() order by sd.depart_order;")
    List<ClockRecord> getTodayAttendance();

    @Select("SELECT " +
            "c.admit_name, c.work_number, c.face_url, c.classes_type, sd.depart_name as dept, ssd.depart_name as classes, " +
            "c.admit_guid, c.in_time, c.out_time, c.abnormal_type, c.solve_reason, c.abnormal, c.solve, c.deputy_supervisor, c.classes_id " +
            "FROM " +
            "clock_record c left join sys_depart sd on sd.id = c.dept left join sys_depart ssd on ssd.id = c.classes " +
            "WHERE " +
            "((DATE(c.date) = CURDATE() and c.classes_type in (1,3)) or (DATE(c.date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) and c.classes_type = 2)) " +
            "order by sd.depart_order;")
    List<ClockRecord> getTodayAttendance2();

    @Select("SELECT " +
            "c.admit_name, c.work_number, c.face_url, c.classes_type, sd.depart_name as dept, " +
            "c.admit_guid, c.in_time, c.out_time, c.abnormal_type, c.solve_reason, c.abnormal, c.solve " +
            "FROM " +
            "clock_record c left join sys_depart sd on sd.id = c.dept " +
            "WHERE " +
            "DATE(c.date) = CURDATE();")
    List<ClockRecord> getToday();

    @Select("select count(*) as value, classes_type as name " +
            "from clock_record " +
            "where date between #{start} and #{end} " +
            "group by classes_type;")
    List<Map<String, Object>> getLeft1(@Param("start") String start, @Param("end") String end);

    @Select("SELECT s.depart_name as dept, COUNT(*) AS total, " +
            "SUM(CASE WHEN c.in_time IS NOT NULL THEN 1 ELSE 0 END) AS in_time_not_null_count, " +
            "ROUND(SUM(CASE WHEN c.in_time IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS percentage " +
            "FROM clock_record c left join sys_depart s on c.dept = s.id " +
            "WHERE date between #{start} and #{end} " +
            "GROUP BY c.dept;")
    List<Map<String, Object>> getLeft2(@Param("start") String start, @Param("end") String end);

    @Select("SELECT date(date) as date, COUNT(*) AS total, " +
            "SUM(CASE WHEN in_time IS NOT NULL THEN 1 ELSE 0 END) AS in_time_not_null_count, " +
            "ROUND(SUM(CASE WHEN in_time IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*) * 100, 2) AS percentage " +
            "FROM clock_record group by date order by date desc limit 5;")
    List<Map<String, Object>> getCenter3();

    @Select("select c.admit_guid, c.admit_name, c.work_number, c.date, c.in_time, " +
            "c.out_time, c.abnormal, c.abnormal_type, c.solve_reason, d.item_text " +
            "from clock_record c left join sys_dict_item d on c.solve_reason = d.item_value " +
            "and d.dict_id = '1885334612388548609' " +
            "WHERE c.admit_guid = #{admitGuid} " +
            "AND c.date >= #{monthStart} " +
            "AND c.date < #{nextMonthStart};")
    List<Map<String, Object>> getPersonInfo(@Param("admitGuid") String admitGuid,
                                            @Param("monthStart") String monthStart,
                                            @Param("nextMonthStart") String nextMonthStart);

}
